/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ca.etsmtl.log660.facade;


import ca.etsmtl.log660.configuration.SessionFactoryHelper;
import ca.etsmtl.log660.entity.Film;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.hibernate.Query;
import org.hibernate.Session;

/**
 *
 * @author iamthegD
 */
public class StatsFacade {

    private static StatsFacade instance = new StatsFacade();
    private Connection connection;

    private StatsFacade(){
        connectionBD();
    }

    public float findCoteByFilmId(int filmId) throws SQLException{

        // On insere la personne dans la BD
        PreparedStatement prep1 = connection.prepareStatement("SELECT COTE_MOYENNE FROM MA_VUE_MOYENNE WHERE IDFILM = "+filmId);

        ResultSet rs = prep1.executeQuery();

        // extract data from the ResultSet
        while (rs.next()) {
            return rs.getFloat(1);
        }

        return -1;
    }
    
    public ArrayList findTopThreeCorrelations(int filmId) throws SQLException{

        // On insere la personne dans la BD
        PreparedStatement prep1 = connection.prepareStatement("SELECT FILM.TITRE FROM MA_VUE_CORRELATION, FILM WHERE IDFILM != "+filmId+
                                                              " AND FILM.IDFILM = MA_VUE_CORRELATION.IDFILM ORDER BY CORR DESC");

        ResultSet rs = prep1.executeQuery();

        ArrayList listeFilm = new ArrayList();
        
        // extract data from the ResultSet
        while (rs.next()) {
            listeFilm.add(rs.getString(1));
        }

        return listeFilm;
    }

    public static StatsFacade getInstance(){
        return instance;
    }

    private void connectionBD() {

            try {

                // On se connecte a la BD
                DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                this.connection = DriverManager.getConnection("jdbc:oracle:thin:@dijkstra.logti.etsmtl.ca:1521:LOG660", "equipe3", "camUDTdV");

                System.out.println("Connection status: "+! this.connection.isClosed());

            } catch (SQLException ex) {
                Logger.getLogger(StatsFacade.class.getName()).log(Level.SEVERE, null, ex);
            }
	}
}
